﻿using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;

public partial class Suppliers : System.Web.UI.Page 
{   

    protected void Page_Load(object sender, EventArgs e)
    {
        FillEmployees();
        btnUpdate.Attributes.Add("onclick", "if(!confirm('Are you sure?'))return false; " );
        btnDelete.Attributes.Add("onclick", "if(!confirm('Are you sure?'))return false; ");
        btnAdd.Attributes.Add("onclick", "if(!confirm('Are you sure?')) return false;");
    }
   
    protected void Button1_Click(object sender, EventArgs e)
    {
        
    }
    protected void TextBox1_TextChanged(object sender, EventArgs e)
    {

    }
    private void ClearAll()
    {
        txtCompanyName.Text = "";
        txtContactName.Text = "";
        txtContactTittle.Text = "";
        txtAddress.Text = "";
        txtCity.Text = "";
        txtPostalCode.Text = "";
        txtRegion.Text = "";
        txtFax.Text = "";
        txtPhone.Text = "";
        DDLSearch.Text = "";
        txtID.Text = "";
        txtSearch.Text = "";
        DDLCountry.SelectedIndex = -1;
    }
    void FillEmployees()
    {
        try
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;

            SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Production.Suppliers", con);
            DataTable dt = new DataTable("getsuppliers");

            da.Fill(dt);

            gvSupplier.AutoGenerateSelectButton = true;
            gvSupplier.AllowPaging = true;
            gvSupplier.PageSize = 10;
            gvSupplier.DataSource = dt;
            gvSupplier.DataBind();
        }
        catch (System.Exception ex)
        {
            
        }
        
    }
   


private void AddNewSupplier()
    {
        try
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "InsertSuppliers";
            {
                SqlParameter param = new SqlParameter("@companyname", txtCompanyName.Text);
                cmd.Parameters.Add(param);
                param = new SqlParameter("@contactname", txtContactName.Text);
                cmd.Parameters.Add(param);
                param = new SqlParameter("@contacttitle", txtContactTittle.Text);
                cmd.Parameters.Add(param);
                param = new SqlParameter("@address", txtAddress.Text);
                cmd.Parameters.Add(param);
                param = new SqlParameter("@city", txtCity.Text);
                cmd.Parameters.Add(param);
                if (txtRegion.Text != "")
                {
                    param = new SqlParameter("@region", txtRegion.Text);
                }
                else param = new SqlParameter("@region", DBNull.Value);
                cmd.Parameters.Add(param);

                param = new SqlParameter("@postalcode", txtPostalCode.Text);
                cmd.Parameters.Add(param);
                param = new SqlParameter("@country", DDLCountry.Text);
                cmd.Parameters.Add(param);
                param = new SqlParameter("@phone", txtPhone.Text);
                cmd.Parameters.Add(param);

                if (txtFax.Text != "")
                {
                    param = new SqlParameter("@fax", txtFax.Text);
                }
                else param = new SqlParameter("@fax", DBNull.Value);

                cmd.Parameters.Add(param);
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
        catch (System.Exception ex)
        {
            
            
        }
}


private void DeleteSupplier()
{
    try
    {
        SqlConnection con = new SqlConnection();
        con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        con.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "DeleteSuppliers";
        SqlParameter param = new SqlParameter("@supplierid", txtID.Text);
        cmd.Parameters.Add(param);
        cmd.ExecuteNonQuery();
    }
    catch (System.Exception ex)
    {
        
        
    }
    
}

private void UpdateSupplier()
{
    try
    {
    SqlConnection con = new SqlConnection();
    con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
    con.Open();
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = con;
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "Updatesupplier";
    txtID.Enabled = false;

    SqlParameter param = new SqlParameter("@supplierid", txtID.Text);
    cmd.Parameters.Add(param);
    param = new SqlParameter("@companyname", txtCompanyName.Text);
    cmd.Parameters.Add(param);
    param = new SqlParameter("@contactname", txtContactName.Text);
    cmd.Parameters.Add(param);
    param = new SqlParameter("@contacttitle", txtContactTittle.Text);
    cmd.Parameters.Add(param);
    param = new SqlParameter("@address", txtAddress.Text);
    cmd.Parameters.Add(param);
    param = new SqlParameter("@city", txtCity.Text);
    cmd.Parameters.Add(param);
    if (txtRegion.Text !="")
    {
        param = new SqlParameter("@region", txtRegion.Text);
    }
    else param = new SqlParameter("@region", DBNull.Value);
    cmd.Parameters.Add(param);
    param = new SqlParameter("@postalcode", txtPostalCode.Text);
    cmd.Parameters.Add(param);
    param = new SqlParameter("@country", DDLCountry.Text);
    cmd.Parameters.Add(param);
    param = new SqlParameter("@phone", txtPhone.Text);
    cmd.Parameters.Add(param);
    if (txtFax.Text != "")
    {
        param = new SqlParameter("@fax", txtFax.Text);
    }
    else param = new SqlParameter("@fax", DBNull.Value);
    cmd.Parameters.Add(param);

    cmd.ExecuteNonQuery();
    con.Close();
    }
    catch (System.Exception ex)
    { 
  
    }
    
}

protected void gvEmployees_PageIndexChanged(object sender, EventArgs e)
{
    
}
protected void DDLCountry_SelectedIndexChanged(object sender, EventArgs e)
{

}
protected void gvEmployees_SelectedIndexChanging(object sender, GridViewSelectEventArgs e)
{

}
protected void gvEmployees_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
    gvSupplier.PageIndex = e.NewPageIndex;
    gvSupplier.DataBind();
}

protected void gvEmployees_SelectedIndexChanged(object sender, EventArgs e)
{   
    try
    {
        btnAdd.Enabled = false;
        GridViewRow r = gvSupplier.SelectedRow;
        txtID.Text = r.Cells[1].Text;
        txtCompanyName.Text = Server.HtmlDecode(r.Cells[2].Text);
        txtContactName.Text = Server.HtmlDecode(r.Cells[3].Text);
        txtContactTittle.Text = Server.HtmlDecode(r.Cells[4].Text);
        txtAddress.Text = Server.HtmlDecode(r.Cells[5].Text);
        txtCity.Text = Server.HtmlDecode(r.Cells[6].Text);
        txtRegion.Text = Server.HtmlDecode(r.Cells[7].Text);
        txtPostalCode.Text = Server.HtmlDecode(r.Cells[8].Text);
        DDLCountry.SelectedItem.Text = Server.HtmlDecode(r.Cells[9].Text);
        txtPhone.Text = Server.HtmlDecode(r.Cells[10].Text);
        txtFax.Text = Server.HtmlDecode(r.Cells[11].Text);
    }
    catch (System.Exception ex)
    {
        
    }
}
protected void txtSearch_TextChanged(object sender, EventArgs e)
{

}



protected void btnNew_Click(object sender, EventArgs e)
{
    ClearAll();
    btnAdd.Enabled = true;
}
protected void btnAdd_Click(object sender, EventArgs e)
{
    AddNewSupplier();
    FillEmployees();
    ClearAll();
}
protected void btnDelete_Click(object sender, EventArgs e)
{   
    DeleteSupplier();
    FillEmployees();
    ClearAll();
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
    UpdateSupplier();
    FillEmployees();
}
protected void btnReload_Click(object sender, EventArgs e)
{
    FillEmployees();
    ClearAll();
}

private void searchCompanyName()
{
    try
    {
        SqlConnection con = new SqlConnection();
        con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        con.Open();


        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "searchCompanyName";


        SqlParameter param = new SqlParameter("@companyname", SqlDbType.NVarChar, 40);
        param.Value = txtSearch.Text;
        cmd.Parameters.Add(param);

        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        DataSet ds = new DataSet();
        da.Fill(ds);


        gvSupplier.DataSource = ds.Tables[0];
        gvSupplier.DataBind();

        con.Close();
    }
    catch (System.Exception ex)
    {

       
    }
}

private void searchContactName()
{
    try
    {
        SqlConnection con = new SqlConnection();
        con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        con.Open();


        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "searchContactName";


        SqlParameter param = new SqlParameter("@contactname", SqlDbType.NVarChar, 30);
        param.Value = txtSearch.Text;
        cmd.Parameters.Add(param);

        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        DataSet ds = new DataSet();
        da.Fill(ds);


        gvSupplier.DataSource = ds.Tables[0];
        gvSupplier.DataBind();

        con.Close();
    }
    catch (System.Exception ex)
    {
        
        
    }
}

private void searchContactTittle()
{
    try
    {
        SqlConnection con = new SqlConnection();
        con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        con.Open();


        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "searchContactTittle";


        SqlParameter param = new SqlParameter("@contacttittle", SqlDbType.NVarChar, 30);
        param.Value = txtSearch.Text;
        cmd.Parameters.Add(param);

        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        DataSet ds = new DataSet();
        da.Fill(ds);


        gvSupplier.DataSource = ds.Tables[0];
        gvSupplier.DataBind();

        con.Close();
    }
    catch (System.Exception ex)
    {
    }
}

private void searchAddress()
{
    try
    {
        SqlConnection con = new SqlConnection();
        con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        con.Open();


        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "searchAddress";


        SqlParameter param = new SqlParameter("@address", SqlDbType.NVarChar, 60);
        param.Value = txtSearch.Text;
        cmd.Parameters.Add(param);

        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        DataSet ds = new DataSet();
        da.Fill(ds);


        gvSupplier.DataSource = ds.Tables[0];
        gvSupplier.DataBind();

        con.Close();
    }
    catch (System.Exception ex)
    {
       
    }
    
}

private void searchCity()
{
    try
    {
        SqlConnection con = new SqlConnection();
        con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        con.Open();


        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "searchCity";


        SqlParameter param = new SqlParameter("@city", SqlDbType.NVarChar, 15);
        param.Value = txtSearch.Text;
        cmd.Parameters.Add(param);

        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        DataSet ds = new DataSet();
        da.Fill(ds);


        gvSupplier.DataSource = ds.Tables[0];
        gvSupplier.DataBind();

        con.Close();
    }
    catch (System.Exception ex)
    {
     
    }
    
}

private void searchRegion()
{
    try
    {
        SqlConnection con = new SqlConnection();
        con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        con.Open();


        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "searchRegion";


        SqlParameter param = new SqlParameter("@region", SqlDbType.NVarChar, 15);
        param.Value = txtSearch.Text;
        cmd.Parameters.Add(param);

        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        DataSet ds = new DataSet();
        da.Fill(ds);


        gvSupplier.DataSource = ds.Tables[0];
        gvSupplier.DataBind();

        con.Close();
    }
    catch (System.Exception ex)
    {
      
    }
    
}

private void searchPostalCode()
{
    try
    {
        SqlConnection con = new SqlConnection();
        con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        con.Open();


        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "searchPostalCode";


        SqlParameter param = new SqlParameter("@postalcode", SqlDbType.NVarChar, 10);
        param.Value = txtSearch.Text;
        cmd.Parameters.Add(param);

        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        DataSet ds = new DataSet();
        da.Fill(ds);


        gvSupplier.DataSource = ds.Tables[0];
        gvSupplier.DataBind();

        con.Close();
    }
    catch (System.Exception ex)
    {
     
    }
    
}

private void searchCountry()
{
    try
    {
        SqlConnection con = new SqlConnection();
        con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        con.Open();


        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "searchCountry";


        SqlParameter param = new SqlParameter("@country", SqlDbType.NVarChar, 15);
        param.Value = txtSearch.Text;
        cmd.Parameters.Add(param);

        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        DataSet ds = new DataSet();
        da.Fill(ds);


        gvSupplier.DataSource = ds.Tables[0];
        gvSupplier.DataBind();

        con.Close();
    }
    catch (System.Exception ex)
    {
      
    }
    
}

private void searchPhone()
{
    try
    {
        SqlConnection con = new SqlConnection();
        con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        con.Open();


        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "searchPhone";


        SqlParameter param = new SqlParameter("@phone", SqlDbType.NVarChar, 24);
        param.Value = txtSearch.Text;
        cmd.Parameters.Add(param);

        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        DataSet ds = new DataSet();
        da.Fill(ds);


        gvSupplier.DataSource = ds.Tables[0];
        gvSupplier.DataBind();

        con.Close();
    }
    catch (System.Exception ex)
    {
     
    }
    
}

private void searchFax()
{
    try
    {
        SqlConnection con = new SqlConnection();
        con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        con.Open();


        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "searchFax";


        SqlParameter param = new SqlParameter("@fax", SqlDbType.NVarChar, 24);
        param.Value = txtSearch.Text;
        cmd.Parameters.Add(param);

        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        DataSet ds = new DataSet();
        da.Fill(ds);


        gvSupplier.DataSource = ds.Tables[0];
        gvSupplier.DataBind();

        con.Close();
    }
    catch (System.Exception ex)
    {
    
    }
    
}


protected void btnSearch_Click(object sender, EventArgs e)
{
    try
    {
        
    string search = DDLSearch.Text;
    if (search == "Company Name")
    {
        searchCompanyName();
    }
    else if (search == "Contact Name")
    {
        searchContactName();
    }
    else if (search == "Contact Tittle")
    {
        searchContactTittle();
    }
    else if (search == "Address")
    {
        searchAddress();
    }
    else if (search == "City")
    {
        searchCity();
    }
    else if (search == "Region")
    {
        searchRegion();
    }
    else if (search == "PostalCode")
    {
        searchPostalCode();
    }
    else if (search == "Country")
    {
        searchCountry();
    }
    else if (search == "Phone")
    {
        searchPhone();
    }
    else if (search == "Fax")
    {
        searchFax();
    }
    }
    catch (System.Exception ex)
    {
        
        throw;
    }

}


protected void txtRegion_TextChanged(object sender, EventArgs e)
{

}
}
